package db;

import model.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBProduct.java
 */
public class DBProduct{
    private  Connection con;
    
    /** Creates a new instance of DBEmployee */
    public DBProduct() {
      con = DBConnection.getInstance().getDBcon();
    }
    
  //Implements the methods from the interface
    // get all Clothes
    public ArrayList<Product> getAllProducts(boolean _retriveAssociation)
    {
        return miscWhere("", _retriveAssociation);
    }
    //get one Product having the id
    public Product getProductID(String _id, boolean _retriveAssociation)
    {   String wClause = " id = '" + _id + "'";
        return singleWhere(wClause, _retriveAssociation);
    }
  
     //insert a new product
    public int insertProduct(Product _product) throws Exception
    {  
       int rc = -1;
	   String query="INSERT INTO Product(id, name, purchasePr, salesPrice, rentPrice, cOrigin, minstock, supplierid, pType)"
	   		+ "  VALUES('"+
		    _product.getId()  + "','"  +
		    _product.getName()  + "','"  +
		    _product.getPurchasePrice() +"','"+ 
		    _product.getSalesPrice() +"','"+
		    _product.getRentPrice()+"','"+
		    _product.getCountry()+"','"+
		    _product.getMinStock()+"','"+
		    _product.getSupplierID()+"','"+
		    _product.getpType()+"')";
                     

       System.out.println("insert : " + query);
      try{ // insert new employee +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Product ikke oprettet");
          throw new Exception ("Product is not inserted correct");
       }
       return(rc);
    }

	public int updateProduct(Product _product)
	{
		Product product  = _product;
		int rc=-1;

		String query="UPDATE Product SET "+
		 	  "id ='"+ product.getId()+"', "+
		 	  "name ='"+ product.getName() + "', " +
		 	  "purchasePr='"+product.getPurchasePrice()+"', "+
		 	  "salesPrice='"+product.getSalesPrice()+"', "+
		 	  "rentPrice='"+product.getRentPrice()+"', "+
		 	  "cOrigin='"+product.getCountry()+"', "+
		 	  "minstock='"+product.getMinStock()+"', "+
		 	  "supplierid='"+product.getSupplierID()+"', "+
		 	  "pType='"+product.getpType()+"' "+
		      " WHERE id = '"+ product.getId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in Product db: "+ex);
	  	}
		return(rc);
	}
	
	public int deleteProduct(String _id)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM Product WHERE id = '" +
				_id + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in Product db: "+ex);
   	        }
		return(rc);
	}
	
	//private methods
    //michWere is used whenever we want to select more than one employee
	
	 
	private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Product> list = new ArrayList<Product>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	Product product = new Product();
		 product = buildProduct(results);	
                 list.add(product);	
		}//end while
                 stmt.close();     
                 /*if(retrieveAssociation)
                 {   //The supervisor and department is to be build as well
                     for(Product custGr : list){
                        String superssn = empObj.getSupervisor().getSsn();
                        Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
                        empObj.setSupervisor(superEmp);
                        System.out.println("Supervisor is seleceted");
                       // here the department has to be selected as well
                        int dno = empObj.getDept().getDnumber();
                        Department department = dbDept.findDepartment(dno, false);
                        empObj.setDepartment(department);
                        System.out.println("Department is selected");
                     }
                 }//end if */  
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one employee 	
	private Product singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Product product = new Product();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the Product from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            product = buildProduct(results);
                            //assocaition is to be build
                            stmt.close();
                            /*if(retrieveAssociation)
                            {   //The supervisor and department is to be build as well
                                String superssn = empObj.getSupervisor().getSsn();
                                Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
                                empObj.setSupervisor(superEmp);
                                System.out.println("Supervisor is seleceted");
                               // here the department has to be selected as well
                                int dno = empObj.getDept().getDnumber();
                                
                                Department department = dbDept.findDepartment(dno, false);
                                
                                empObj.setDepartment(department);
                                System.out.println("Department is selected");
                           
                            }*/
			}
                        else{ //no employee was found
                            product = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return product;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT id, name, purchasePr, salesPrice, rentPrice, cOrigin, minstock, supplierid, pType from Product";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build a product object
	private Product buildProduct(ResultSet results)
      {   Product product = new Product();
          try{ 
        	    product.setId(Integer.parseInt(results.getString("id")));
        	    product.setName(results.getString("name"));
        	    product.setPurchasePrice(Double.parseDouble(results.getString("purchasePr")));
        	    product.setSalesPrice(Double.parseDouble(results.getString("salesPrice")));
        	    product.setRentPrice(Double.parseDouble(results.getString("rentPrice")));
        	    product.setCountry(results.getString("cOrigin"));
        	    product.setMinStock(Integer.parseInt(results.getString("minstock")));
        	    product.setSupplierID(Integer.parseInt(results.getString("supplierId")));
        	    product.setpType(results.getString("pType"));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Product object");
         }
         return product;
      }

 
}  